import pymysql
import json

mysqlCon = pymysql.Connect(
    host='rm-bp10684y5104946w7o.mysql.rds.aliyuncs.com',
    user='root',
    password="Xmtj6666",
    database='lolita',
    port=3306,
    charset='utf8mb4',
)
mysqlCur = mysqlCon.cursor()

sql = "INSERT INTO `lo_post` (`created_at`,`updated_at`,`user_id`,`title`,`is_original`,`images`,`video_src` ,`video_cover`,`video_length`,`source`,`original_date`,`original_author`,`original_author_url`,`content`,`type`) VALUES (%s,%s,%s, %s,%s, %s,%s, %s, %s,%s, %s,%s, %s,%s, %s)"

queryBiliSql = "SELECT `title`,`description`,`cover`,`src`,`author`,`author_url`,`length` ,`date` FROM `lo_data_bilibili`"

queryBiliPostSrc = "SELECT `video_src` FROM `lo_post` WHERE `video_src`=%s"


def saveBili2Post(date, title, src, cover, length, author, author_url, description):
    i = 1
    comefrom = 'bilibili'
    c = (date, date, 1, title, False, None, src, cover, length, comefrom, date, author, author_url, description, 2)
    mysqlCur.execute(sql, c)
    mysqlCon.commit()
    print("bilibili已经添加到post了%s条,[%s]" % (i, title))
    i += 1


def updateBili2Post():
    i = 1
    mysqlCur.execute(queryBiliSql)
    alls = mysqlCur.fetchall()
    for one in alls:
        src = one[3]
        mysqlCur.execute(queryBiliPostSrc, src)
        ifExist = mysqlCur.fetchone()
        if ifExist:
            print('已经储存了该视频信息')
            break
        title = one[0]
        description = one[1]
        cover = one[2]
        author = one[4]
        author_url = one[5]
        length = one[6]
        date = one[7]
        comefrom = 'bilibili'
        c = (date, date, 1, title, False, None, src, cover, length, comefrom, date, author, author_url, description, 2)
        mysqlCur.execute(sql, c)
        mysqlCon.commit()
        print("bilibili已经添加到post了%s条,[%s]" % (i, title))
        i += 1


# querySinaSql = "SELECT `text`,`images`,`author` ,`date` FROM `lo_data_sina` LIMIT 5"
querySinaSql = "SELECT `text`,`images`,`author` ,`date` FROM `lo_data_sina`"

querySinaPostSrc = "SELECT `original_author` FROM `lo_post` WHERE `original_date`=%s"


def saveSina2Post(images, date, author, text):
    i = 1
    imgObj = json.loads(images)
    imagesStr = ""
    for img in imgObj:
        imagesStr = imagesStr + "http://" + img + ","
    comefrom = '新浪微博'
    # `created_at`,`updated_at`,`user_id`,`title`,`is_original`,`images`,`video_src` ,`video_cover`,`video_length`,`source`,`original_date`,`original_author`,`original_author_url`,`content`,`type`
    c = (date, date, 1, None, False, imagesStr[:-1], None, None, None, comefrom, date, author, None, text, 1)
    mysqlCur.execute(sql, c)
    mysqlCon.commit()
    print("新浪微博已经添加到post了%s条,[%s]" % (i, author))
    i += 1


def updateSina2Post():
    i = 1
    mysqlCur.execute(querySinaSql)
    alls = mysqlCur.fetchall()
    for one in alls:
        date = one[3]
        mysqlCur.execute(querySinaPostSrc, date)
        ifExist = mysqlCur.fetchone()
        if ifExist:
            print('已经储存了该微博信息:', ifExist)
            break

        text = one[0]
        images = one[1]
        author = one[2]
        imgObj = json.loads(images)
        imagesStr = ""
        for img in imgObj:
            imagesStr = imagesStr + "http://" + img + ","
        comefrom = '新浪微博'
        # `created_at`,`updated_at`,`user_id`,`title`,`is_original`,`images`,`video_src` ,`video_cover`,`video_length`,`source`,`original_date`,`original_author`,`original_author_url`,`content`,`type`
        c = (date, date, 1, None, False, imagesStr[:-1], None, None, None, comefrom, date, author, None, text, 1)
        mysqlCur.execute(sql, c)
        mysqlCon.commit()
        print("新浪微博已经添加到post了%s条,[%s]" % (i, author))
        i += 1


def updatePost():
    updateBili2Post()
    updateSina2Post()


if __name__ == "__main__":
    updatePost()
